class Project < ActiveRecord::Base
   has_many :messages, :order => 'id DESC',:dependent=>:destroy
   has_many :todos, :order => 'id DESC',:dependent=>:destroy

   has_many :milestones, :order => 'due_at',:dependent=>:destroy
   has_many :late_milestones,:class_name=>'Milestone',:conditions=>["(finished_at = 0 or finished_at is NULL) AND due_at < date_add(now(),INTERVAL -1 day)"],:order => "due_at"
   has_many :upcoming_milestones,:class_name=>'Milestone',:conditions=>["(finished_at = 0 or finished_at is NULL) AND due_at > now()"],:order => "due_at"
   has_many :upcoming_milestones_14,:class_name=>'Milestone',:conditions=>["(finished_at = 0 or finished_at is NULL) AND due_at > date_add(now(),INTERVAL -1 day) and due_at < date_add(now(),INTERVAL 14 DAY)"],:order => "due_at"
   has_many :finished_milestones,:class_name=>'Milestone',:conditions=>["finished_at > 0"],:order => "due_at"

   has_many :logs, :order => 'id DESC',:dependent=>:destroy
   has_many :msg_categories,  :class_name =>'Category', :conditions=>"label = 'msg' or label is NULL", :order => 'id DESC',:dependent=>:destroy
   has_many :file_categories, :class_name => 'Category', :conditions=>"label = 'files'", :order => 'id DESC',:dependent=>:destroy
   has_many :wb_categories, :class_name => 'Category', :conditions=>"label = 'writeboards'", :order => 'id DESC',:dependent=>:destroy
   has_many :time_logs,:order=>'id DESC',:dependent=>:destroy
   has_many :tasks

   has_many :users, :through => :tasks, :order=>'users.username'
   has_many :logs,:dependent=>:destroy
   has_many :writeboards,:dependent=>:destroy
   has_many :files, :class_name=>'Files',:dependent=>:destroy
   has_and_belongs_to_many :activity_types
   belongs_to :company 
   
   validates_presence_of :name

  def roled_users(role_id)
     User.find_by_sql("SELECT users.* from users,tasks,roles_users where tasks.project_id = #{self.id} and roles_users.role_id = #{role_id} and tasks.user_id = users.id and roles_users.user_id = users.id")
  end

  def custom_logs(n)
    Log.find(:all,:conditions=>"project_id = #{self.id}",:limit=>n,:order => 'id desc')
  end
  def related_milestones(todo)
    if todo.milestone.nil?
      if self.todos.size > 0
      self.milestones - Milestone.find_by_sql("SELECT * FROM milestones where id in (#{self.todos.collect{|t| t.milestone_id.to_i}.join(",")})")
      else
       self.milestones
      end
    else
      if self.todos.size > 0
       self.milestones - Milestone.find_by_sql("SELECT * FROM milestones where id in (#{self.todos.collect{|t| t.milestone_id.to_i}.join(",")})") + [todo.milestone]
      else
       self.milestones
      end
    end
  end
  def has_user?(id)
     users.to_a.find{|u| u.id == id} 
  end

  def has_activity?(id)
     activity_types.to_a.find{|a| a.id == id} 
  end

  def user_is_manager?(id)
     tasks.to_a.find{|t| t.user.id == id and t.role == "Manager"} 
  end

  def activity_type_ids= (lists) 
    atypes = self.activity_types
    atypes.clear
    lists.each do |l|
      atypes << ActivityType.find(l)
    end
  end

  def users_ids= (lists) 
    tasks = self.tasks
    tasks.clear
    lists.each do |l|
	  if Thread.current[:role][l] == "m"
         task = Task.create(:project=>self,:user=> User.find(l),:role=>'Manager')
	  else
         task = Task.create(:project=>self,:user=> User.find(l),:role=>'Employee')
	  end
    tasks << task 
    end
    null_tasks = Task.find_by_sql "SELECT * FROM tasks WHERE project_id is NULL"
    null_tasks.each do |ntask| 
      ntask.destroy
    end
  end

  #
  #
  #
  def hours(*args)
	self.reload
	total = 0 

	#sum all period of time
	if args.length == 1
	  user_id = args[0]
	  ts = TimeLog.find_by_sql("SELECT SUM(TIME_TO_SEC(end_at) - TIME_TO_SEC(start_at))/3600 as sum FROM time_logs WHERE user_id = #{user_id} AND project_id = #{self.id}")
    elsif args.length == 3
	  user_id = args[0]
	  start_s = args[1]
	  end_s   = args[2]
	  ts = TimeLog.find_by_sql("SELECT SUM(TIME_TO_SEC(end_at) - TIME_TO_SEC(start_at))/3600 as sum FROM time_logs WHERE " + " user_id = #{user_id} " + " AND project_id = #{self.id}" + " AND start_at > '#{start_s}'" + " AND end_at < '#{end_s}'")
	  total = ts.first.sum.to_i
    elsif args.length == 4
	  user_id = args[0]
	  atype   = args[1]
	  start_s = args[2]
	  end_s   = args[3]
	  ts = TimeLog.find_by_sql("SELECT SUM(TIME_TO_SEC(end_at) - TIME_TO_SEC(start_at))/3600 as sum FROM time_logs WHERE " + " user_id = #{user_id} " + " AND activity_type_id = #{atype}" + " AND project_id = #{self.id}" + " AND start_at > '#{start_s}'" + " AND end_at < '#{end_s}'")
    elsif args.length == 5
	  user_id = args[0]
	  start_s = args[1]
	  end_s   = args[2]
	  confirmed_time = args[3]
	  work_time      = args[4]
	  cfm_sql = ""
	  if confirmed_time == "confirmed time"
	     cfm_sql = " AND confirmed = 1 "
	  elsif confirmed_time == "unconfirmed time"
	     cfm_sql = " AND confirmed = 0 "
	  else
	     cfm_sql = " "
	  end
	  work_sql = ""
	  if work_time == "work time"
	     work_sql = " AND after_work_time = 0 "
	  elsif work_time == "after work time"
	     work_sql = " AND after_work_time = 1 "
	  else
	     work_sql = ""
	  end
	  ts = TimeLog.find_by_sql("SELECT SUM(TIME_TO_SEC(end_at) - TIME_TO_SEC(start_at))/3600 as sum FROM time_logs WHERE " + " user_id = #{user_id} " + cfm_sql + work_sql +  " AND project_id = #{self.id}" + " AND start_at > '#{start_s}'" + " AND end_at < '#{end_s}'")
    elsif args.length == 6
	  user_id = args[0]
	  a_id    = args[1]
	  start_s = args[2]
	  end_s   = args[3]
	  confirmed_time = args[4]
	  work_time      = args[5]
	  cfm_sql = ""
	  if confirmed_time == "confirmed time"
	     cfm_sql = " AND confirmed = 1 "
	  elsif confirmed_time == "unconfirmed time"
	     cfm_sql = " AND confirmed = 0 "
	  else
	     cfm_sql = " "
	  end
	  work_sql = ""
	  if work_time == "work time"
	     work_sql = " AND after_work_time = 0 "
	  elsif work_time == "after work time"
	     work_sql = " AND after_work_time = 1 "
	  else
	     work_sql = ""
	  end
	  ts = TimeLog.find_by_sql("SELECT SUM(TIME_TO_SEC(end_at) - TIME_TO_SEC(start_at))/3600 as sum FROM time_logs WHERE " + " user_id = #{user_id} " + " AND activity_type_id = #{a_id}" + cfm_sql + work_sql +  " AND project_id = #{self.id}" + " AND start_at > '#{start_s}'" + " AND end_at < '#{end_s}'")
    elsif args.length == 7
	  user_id = args[0]
	  atype   = args[1]
	  start_s = args[2]
	  end_s   = args[3]
	  confirmed_time = args[4]
	  work_time      = args[5]
	  cfm_sql = ""
	  if confirmed_time == "confirmed time"
	     cfm_sql = " AND confirmed = 1 "
	  elsif confirmed_time == "unconfirmed time"
	     cfm_sql = " AND confirmed = 0 "
	  else
	     cfm_sql = ""
	  end
	  work_sql = ""
	  if work_time == "work time"
	     work_sql = " AND after_work_time = 0 "
	  elsif work_time == "after work time"
	     work_sql = " AND after_work_time = 1 "
	  else
	     work_sql = ""
	  end
	  ts = TimeLog.find_by_sql("SELECT SUM(TIME_TO_SEC(end_at) - TIME_TO_SEC(start_at))/3600 as sum FROM time_logs WHERE " + " user_id = #{user_id} " + cfm_sql + work_sql + " AND activity_type_id = #{atype}" + " AND project_id = #{self.id}" + " AND start_at > '#{start_s}'" + " AND end_at < '#{end_s}'")
	end
	  total = ts.first.sum.to_f

	return total 
  end
  def total_hours(*args)
	self.reload
    total = 0

	if args.length == 0 
	  ts = TimeLog.find_by_sql("SELECT SUM(TIME_TO_SEC(end_at) - TIME_TO_SEC(start_at))/3600 as sum FROM time_logs WHERE project_id = #{self.id}")
	  #ts = TimeLog.find_by_sql("SELECT SUM(TIME_TO_SEC(end_at) - TIME_TO_SEC(start_at))/3600 as sum FROM time_logs WHERE project_id = #{self.id} AND confirmed = 1")
	  total = ts.first.sum.to_f
	elsif args.length == 1 
	  if args[0] == "all"
	    ts = TimeLog.find_by_sql("SELECT SUM(TIME_TO_SEC(end_at) - TIME_TO_SEC(start_at))/3600 as sum FROM time_logs WHERE " + " project_id = #{self.id}")
	  elsif args[0] == "unconfirmed"
	    ts = TimeLog.find_by_sql("SELECT SUM(TIME_TO_SEC(end_at) - TIME_TO_SEC(start_at))/3600 as sum FROM time_logs WHERE " + " project_id = #{self.id}" + " AND confirmed = 0")
	  elsif args[0] == "confirmed"
	    ts = TimeLog.find_by_sql("SELECT SUM(TIME_TO_SEC(end_at) - TIME_TO_SEC(start_at))/3600 as sum FROM time_logs WHERE " + " project_id = #{self.id}" + " AND confirmed = 1")
	  end
	elsif args.length == 2 
	  start_s = args[0]
	  end_s   = args[1]
	  ts = TimeLog.find_by_sql("SELECT SUM(TIME_TO_SEC(end_at) - TIME_TO_SEC(start_at))/3600 as sum FROM time_logs WHERE " + " project_id = #{self.id}" + " AND start_at > '#{start_s}'" + " AND end_at < '#{end_s}'")
	elsif args.length == 3 
	  atype = args[0]
	  start_s = args[1]
	  end_s   = args[2]
	  ts = TimeLog.find_by_sql("SELECT SUM(TIME_TO_SEC(end_at) - TIME_TO_SEC(start_at))/3600 as sum FROM time_logs WHERE " + " project_id = #{self.id}" + " AND activity_type_id = #{atype}" + " AND start_at > '#{start_s}'" + " AND end_at < '#{end_s}'")
	elsif args.length == 4 
	  start_s = args[0]
	  end_s   = args[1]
	  confirmed_time = args[2]
	  work_time      = args[3]
	  cfm_sql = ""
	  if confirmed_time == "confirmed time"
	     cfm_sql = " AND confirmed = 1 "
	  elsif confirmed_time == "unconfirmed time"
	     cfm_sql = " AND confirmed = 0 "
	  else
	     cfm_sql = ""
	  end
	  work_sql = ""
	  if work_time == "work time"
	     work_sql = " AND after_work_time = 0 "
	  elsif work_time == "after work time"
	     work_sql = " AND after_work_time = 1 "
	  else
	     work_sql = ""
	  end
	  ts = TimeLog.find_by_sql("SELECT SUM(TIME_TO_SEC(end_at) - TIME_TO_SEC(start_at))/3600 as sum FROM time_logs WHERE " + " project_id = #{self.id}" + cfm_sql + work_sql + " AND start_at > '#{start_s}'" + " AND end_at < '#{end_s}'")
	elsif args.length == 5 
	  atype = args[0]
	  start_s = args[1]
	  end_s   = args[2]
	  confirmed_time = args[3]
	  work_time      = args[4]
	  cfm_sql = ""
	  if confirmed_time == "confirmed time"
	     cfm_sql = " AND confirmed = 1 "
	  elsif confirmed_time == "unconfirmed time"
	     cfm_sql = " AND confirmed = 0 "
	  else
	     cfm_sql = ""
	  end
	  work_sql = ""
	  if work_time == "work time"
	     work_sql = " AND after_work_time = 0 "
	  elsif work_time == "after work time"
	     work_sql = " AND after_work_time = 1 "
	  else
	     work_sql = ""
	  end
	  ts = TimeLog.find_by_sql("SELECT SUM(TIME_TO_SEC(end_at) - TIME_TO_SEC(start_at))/3600 as sum FROM time_logs WHERE " + " project_id = #{self.id}" + cfm_sql + work_sql + " AND activity_type_id = #{atype}" + " AND start_at > '#{start_s}'" + " AND end_at < '#{end_s}'")
	end
	  total = ts.first.sum.to_f
	return total 
  end
  def uncat_msg_size
    Message.find(:all,:conditions=>["project_id=? and (category_id is null or category_id = 0)",self.id]).size
  end
  def uncat_file_size
    Files.find(:all,:conditions=>["project_id=? and (category_id is null or category_id = 0)",self.id]).size
  end
  def uncat_wb_size
    Writeboard.find(:all,:conditions=>["project_id=? and (category_id is null or category_id = 0)",self.id]).size
  end
end
